# -*- coding: utf-8 -*-

import sybase
import commands
import time

con = sybase.connect("root", "", "NETCOOL")
cursor = con.cursor()

cursor.execute("select count(*) as num from alerts.status where (AlertGroup = 'LINEPROTO-5-UPDOWN' or AlertGroup = 'LINK-3-UPDOWN' or AlertGroup = 'LINEPROTO-SP-5-UPDOWN' or AlertGroup = 'LINEPROTO-SP-UPDOWN' or AlertGroup = 'LINK-SP-3-UPDOWN' or AlertGroup = 'LINK-Status' or AlertGroup = 'LINEPROTO-UPDOWN') and Severity >= 4")
for num in cursor.fetchall():
	#print num[0]
	if int(num[0])<1000:
		cursor.execute("select Node,AlertKey,Serial from alerts.status where (AlertGroup = 'LINEPROTO-5-UPDOWN' or AlertGroup = 'LINK-3-UPDOWN' or AlertGroup = 'LINEPROTO-SP-5-UPDOWN' or AlertGroup = 'LINK-SP-3-UPDOWN' or AlertGroup = 'LINEPROTO-SP-UPDOWN' or AlertGroup = 'LINK-Status' or AlertGroup = 'LINEPROTO-UPDOWN') and Severity >= 4")

		for Node,AlertKey,Serial in cursor.fetchall():
			#print Node,AlertKey,Serial
			resultport = commands.getoutput("snmpwalk "+Node+" -c cffex2006RO -v 2c .1.3.6.1.2.1.2.2.1.2")
			if "Timeout" not in resultport:
				result = resultport.split("\n")
				i = 0
				while i < len(result):
					temp = result[i].split()
					if temp[3] == AlertKey:
						index = temp[0].split('.')[1]
						resultstatus = commands.getoutput("snmpwalk "+Node+" -c cffex2006RO -v 2c .1.3.6.1.2.1.2.2.1.8."+index)
						if "up" in resultstatus:
							#print Node,AlertKey,Serial
							cursor.execute("update alerts.status set Severity = 0 where Serial = "+str(Serial)+"")
					i = i + 1	





################### add by hfh for uplevel 20121119 ##########

strs = "update status set Severity = 4 where Severity < 4 and Severity >0 and (NodeAlias like 'W_JR_' or NodeAlias like 'W_JY_' or NodeAlias like 'W_YW_') and (NodeAlias not like '_WW_') and AlertGroup not in ('ses [Added|Deleted|Modified]','AuditLogQueue','Addresses group [Added|Deleted|Modified]','Admin - Account Password','Admin - Console Page size','Auth - Server Connectivity Failure','Auth - Server Connectivity Test','Auth Login Failed','Auth Radius - [Rejected|Challenged|Accepted]','Auth TACACS - [Rejected|Challenged|Accepted]','C6K_PLATFORM-STDBY-CONFREG_BREAK_ENABLED**','CLEAR-COUNTERS','CLKCHANGE','CRYPTO-GDOI_ON_OFF**','CRYPTO-ISAKMP_ON_OFF**','ENTITY','FABRIC-SP-CLEAR_BLOCK**','FABRIC-SP-FABRIC_MODULE_BACKUP**','FABRIC-STDBY-FABRIC_MODULE_ACTIVE**','FIB-FIBXDRINV','FILESYS-CF','HA-NSRP - HA Synchronization File','Logging - Event Log (Reviewed)','Logging - System Log (Reviewed)','Logging - traffic Log Alarms and Events [Cleared]','mgmt','NSRD - Rapid Deployment Status','NTP - NTP Server Connection (Failure)','NTP - Setting (Changed)','NTP - System Clock Update','NTP_NTP_LOG','OIR-CONSOLE','OIR-SP-DOWNGRADE_EARL**','OIR-STDBY-CONSOLE','ONLINE-SP-DNLDFAIL**','OSPF_MAXAGE_LSA','PFINIT-SP-CONFIG_SYNC**','PFREDUN-SP-ACTIVE**','PFREDUN-STANDBY**','PFREDUN-STDBY-STANDBY**','PKI - Certificate Validity','Policy - Destination Address [Added|Deleted]','Policy - Policy Management','Policy - Policy Position','Policy - Service [Added|Deleted]','Policy - Source Address [Added|Deleted]','secure','Service - Service Group [Added|Modified|Deleted]','SHELL_LOGINAUTHFAIL','SHELL_LOGINFAIL','SHELL-5_LOGIN','SHELL-5_LOGOUT','SNMP-CHASSISALARM**','SNMP-COLDSTART**','SNMP-MODULETRAP**','SSH-4-LOGOUT','SSHv2 - Password Authentication Status','SYS-CLOCKUPDATE**','SYS-CONFIG_I','SYS-LOGGER_FLUSHED','SYS-LOGGINGHOST_STARTSTOP**','SYS-SP-LOGGER_FLUSHED','SYS-STDBY-BOOTTIME','SYS-STDBY-LOGGER_FLUSHED','system','System - .hash-seg Variable [Set|Unset|Changed]','System - last_reset Variable [Set|Unset|Changed]','System - Lock Configuration Status','System - Operational Status','System - Output Redirected','System - System Configuration (Saved)','VPN_HW-INFO_LOC**','Web Login Failed','ADM','Admin monitor is locked','ADM-Login-Web','ADM-Remote','AuditLogOverwritten','AuthServercreated','AuthServerFailOver','AuthServerModified','AuthServerNameSet','AuthServerNameUnset','AuthServerRadiusChanged','AuthServerTimeOut','AuthServerTypeSet','Bad IP option protection','DNS proxy was disabled on interfac','Dropping pkts if not exactly same with incoming','Dropping pkts if not in route table','FIN bit but no ACK bit protection','Fragmented packet protection','ICMP flood protection','ICMP fragment protection','ICMP ping id zero protection ','ICMP ping id=0','IP address','IP spoof attack protection','IP sweep protection','Land attack protection','Large ICMP packet protection','Loose src route IP option detection','Mapped IP Added','NSRP black hole prevention','NTP server is disabled on interface','NumberofRadius','Ping of Death attack protection','Port scan protection','Record route IP option detection','Security IP option detection','Session limit threshold for same dst IP','Session limit threshold for same src IP','Src IP-based session limiting','SSL has been enabled on interface','Stream IP option detection','Strict src route IP option detection','SYN and FIN bits set protection','SYN flood alarm threshold','SYN flood attack threshold','SYN flood protection','SYN flood same destination ip threshold','SYN flood same source ip threshold','SYN fragment protection','SYN-ACK-ACK proxy protection','SYN-ACK-ACK proxy threshold','TCP packet without flags protection','Telnet has been disabled on interface','Telnet has been enabled on interface','Timestamp IP option detection','TrialKeys','TurnOffDebug','UDP flood protection','Unknown protocol protection','Web has been disabled on interface','WinNuke attack protection','monitor turn off debug','ICMP PING','Attacks - Src IP session limit')"

cursor.execute(strs)

########### edit by hfh 20121119 end #############

########### add by hfh for uplevel TrdSeatOrderWrn events 20130724 #########################
strs = "select Serial,Summary from status where AlertGroup = 'TrdSeatOrderWrn' and Tally > 200 and LastOccurrence >= StateChange"
cursor.execute(strs)
for Serial,Summary in cursor.fetchall():
	TempS = "请联系会员岗位：" + str(Summary)
	st = "update status set Summary='" + str(TempS) + "' where Serial = " + str(Serial) + " "
	cursor.execute(st)
strs = "update status set Severity = 4 where AlertGroup = 'TrdSeatOrderWrn' and Tally > 200 and LastOccurrence > StateChange"
cursor.execute(strs)
############################################################################################


############## add by hfh for uplevel Src IP Session Limit ##########################
def lc_schedule(schedule):

	beginweekday=int(schedule.split(',')[0].split('-')[0])
	endweekday=int(schedule.split(',')[0].split('-')[1])
	beginhour=int(schedule.split(',')[1].split('-')[0].split(':')[0])
	beginminute=int(schedule.split(',')[1].split('-')[0].split(':')[1])
	endhour=int(schedule.split(',')[1].split('-')[1].split(':')[0])
	endminute=int(schedule.split(',')[1].split('-')[1].split(':')[1])
		
	current_weekday=time.localtime()[6]
	current_hour=time.localtime()[3]
	current_minute=time.localtime()[4]
	
	current_time=current_hour*60+current_minute
	begintime=beginhour*60+beginminute
	endtime=endhour*60+endminute
	
	if current_weekday < beginweekday or current_weekday > endweekday:
		return False
	if current_time < begintime or current_time > endtime:
		return False
	return True

tconf1='0-4,9:15-11:30'
tconf2='0-4,13:00-15:15'

if lc_schedule(tconf1) or lc_schedule(tconf2):
	strs = "update status set Severity=4,Tally=1 where AlertGroup = 'Attacks - Src IP session limit' and Tally > 20 and LastOccurrence >= StateChange and Severity = 3"
	cursor.execute(strs)
############################################################################################


cursor.close()
con.close()


